uncategorized

Python处理excel

Excel Python

摘要：本文主要记录一下Python处理excel表格的一些常用包，包括读取模块xlrd,写入模块xlwt和xlsxwriter

1. xlrd

note:

  • xlrd可以读取xls(2003)、xlsx(2007及以上)
  • xls的行数限制为65536，最大列数是256列；xlsx的行数限制为1048576，最大列数是1638列

基本操作：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# 打开Excel表格
workbook = xlrd.open_workbook(r'D:\programming\pycharm\pythonProject\helic\test.xlsx')

# 获取所有sheet  
print workbook.sheet_names() # [u'sheet1', u'sheet2']  
sheet2_name = workbook.sheet_names()[1]

# 根据sheet索引或者名称获取sheet内容  
sheet2 = workbook.sheet_by_index(1) # sheet索引从0开始  
sheet2 = workbook.sheet_by_name('sheet2')

# sheet的名称，行数，列数  
print sheet2.name,sheet2.nrows,sheet2.ncols

# 获取整行和整列的值（数组）  
rows = sheet2.row_values(3) # 获取第四行内容  
cols = sheet2.col_values(2) # 获取第三列内容  

# 获取单元格内容  
print sheet2.cell(1,0).value.encode('utf-8')  
print sheet2.cell_value(1,0).encode('utf-8')  
print sheet2.row(1)[0].value.encode('utf-8')  

# 获取单元格内容的数据类型，类型 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
print sheet2.cell(1,0).ctype

python读取excel中单元格类型为date(有的日期是以string格式存储，无需用此方式读取，依照上述基本操作)；date类型的ctype=3，这时需要使用xlrd的xldate_as_tuple来处理为date格式，先判断表格的ctype,如果等于3，则用时间格式处理:

if sheet.cell(row,col).ctype == 3:         
    date_value = xlrd.xldate_as_tuple(sheet.cell_value(rows,3),workbook.datemode)      # (1992, 2, 22, 0, 0, 0)
    date_tmp = date(*date_value[:3]).strftime('%Y/%m/%d') # '1992/02/22'

读取合并单元格的内容(合并的单元格只是合并的第一个单元格的有值，其它的为空;只能获取合并单元格的第一个cell的行列索引，才能读到值;合并行单元格读取行的第一个索引，合并列单元格读取列的第一个索引):

1
2
print sheet2.col_values(4)[1]  # 好朋友
print sheet2.row_values(7)[2]  # 暂无

获取合并单元格的位置:读取文件的时候需要将formatting_info参数设置为True，默认是False

1
2
3
workbook = xlrd.open_workbook(r'F:\demo.xlsx',formatting_info=True)  
sheet2 = workbook.sheet_by_name('sheet2')  
print sheet2.merged_cells  # [(7, 8, 2, 5), (1, 3, 4, 5), (3, 6, 4, 5)]

merged_cells返回的这四个参数的含义是：(row,row_range,col,col_range),其中[row,row_range)包括row,不包括row_range,col也是一样，即(1, 3, 4, 5)的含义是：第2到3行（不包括第4行）合并，(7, 8, 2, 5)的含义是：第3到5列合并。获取merge_cells返回的row和col低位的索引即可

1
2
3
4
5
6
7
8
9
print sheet2.cell_value(1,4)  #(1, 3, 4, 5)  好朋友  
print sheet2.cell_value(3,4)  #(3, 6, 4, 5)  同学  
print sheet2.cell_value(7,2)  #(7, 8, 2, 5)  暂无

merge = []  
for (rlow,rhigh,clow,chigh) in sheet2.merged_cells:  # [(7, 8, 2, 5), (1, 3, 4, 5), (3, 6, 4, 5)]
    merge.append([rlow,clow])
for index in merge:   # [[7, 2], [1, 4], [3, 4]]  
    print sheet2.cell_value(index[0],index[1])

2. xlwt 与 xlsxwriter

  • xlwt只能写.xls(2003)文件，xlsxwriter只能写.xlsx(2007及以后)表格

( 1 ) xlwt

1
2
3
4
5
6
7
8
style0 = xlwt.easyxf('font: name 等线, color-index black, bold off, height 220')
style1 = xlwt.easyxf('font: name 等线, color-index red, bold off, height 220')

f = xlwt.Workbook()                                      # 创建工作簿
female = f.add_sheet(u'female', cell_overwrite_ok=True)  # 创建sheet
female.write(0, 0, 'test', style1)
female.write(0, 1, 'test', style0)
f.save("demo.xls")

( 2 ) xlsxwriter

1
2
3
4
5
6
7
workbook = xlsxwriter.Workbook('hello.xlsx')  # 建立文件
# Add a bold format to use to highlight cells. 设置粗体，默认是False
bold = workbook.add_format({'bold': False, 'color': 'black', 'font_name': '等线', 'font_size': '11'})
# 建立sheet， 可以work.add_worksheet('employee')来指定sheet名，但中文名会报UnicodeDecodeErro的错误
worksheet = workbook.add_worksheet('female')
worksheet.write(0, 0, 'Hello world', bold)  # 向A1写入
workbook.close()
Share